package com.docman.dao;

import java.sql.*;
import java.util.*;
import java.util.Date;

import com.docman.dbconnect.*;
import com.docman.domainmodel.DocumentBean;
import com.docman.domainmodel.UserBean;

public class DocumentDAO {

	static Connection currentCon = null;
	static ResultSet rs = null;
	
	
	public static void downloadFile(DocumentBean document){
		
	}
	public static ArrayList<DocumentBean> searchFile(String query){
		ArrayList<DocumentBean> documentList = new ArrayList<DocumentBean>();
		
		
		String statement ="SELECT DISTINCT * FROM DOCUMENTS WHERE title LIKE '%" +query+ "%'  OR docName LIKE '%" +query+ "%' ORDER BY docName";
	
		try{
			currentCon =ConnectionManager.getInstance().getConnection();
			Statement stmt =currentCon.createStatement();
			rs= stmt.executeQuery(statement);
			
			
			
			
			while (rs.next()){
			
			UserBean user = new UserBean();
			DocumentBean doc =new DocumentBean();
			doc.setDocId(rs.getInt("docId"));
			doc.setComment(rs.getString("comment"));
			doc.setDocName(rs.getString("docName"));
			doc.setRating(rs.getInt("totalRating"));
			doc.setRatingCount(rs.getInt("ratingCount"));
			doc.setTag(rs.getString("tag"));
			doc.setTitle(rs.getString("title"));
			doc.setUploadDate(rs.getDate("uploadDate"));
			
			user.setUserName(rs.getString("uploader"));
			doc.setUploader(user.getUserName());
			
			doc.setDocPath(rs.getString("filePath"));
			documentList.add(doc);
			
			}
			
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			
			
			System.out.println("Search Failed "+e);
		}
		
		
		
		return documentList;
		
	}
	public static void uploadFile(DocumentBean document){
		
		PreparedStatement stmt = null;
		
		String title = document.getTitle();
		String docName = document.getDocName();
		String comment = document.getComment();
		String tag = document.getTag();
		int docId = document.getDocId();
		String uploader = document.getUploader();
		java.sql.Date uploadDate = document.getUploadDate();
		String filePath = document.getDocPath();
		int totalRating = document.getRating();
		int ratingCount = document.getRatingCount();
		
		String query = "INSERT INTO DOCUMENTS(title, docName, comment, tag, docId, uploader, uploadDate, filePath, totalRating, ratingCount) " +
				"VALUES(?, ?, ?, ?, ?, ?, ?, ? ,? ,? )";
		try
		{
			ConnectionManager mgr = ConnectionManager.getInstance();
			currentCon = mgr.getConnection();

			
			
			stmt = currentCon.prepareStatement(query);  
			stmt.setString(1, title); 
			stmt.setString(2, docName);
			stmt.setString(3, comment);
			stmt.setString(4, tag);
			stmt.setInt(5, docId);
			stmt.setString(6, uploader);
			stmt.setDate(7,  uploadDate);
			stmt.setString(8, filePath);
			stmt.setInt(9, totalRating);
			stmt.setInt(10, ratingCount);
			stmt.executeUpdate();
	
			stmt.close();
		
		}

		catch(Exception e)
		{
			System.out.println("Upload Failed "+e);
		}

	}
	
public static DocumentBean getDocInfo(int docId){
		
		DocumentBean doc = new DocumentBean();
		UserBean user = new UserBean();
		
		String statement ="SELECT * FROM DOCUMENTS WHERE docId="+ docId;
		
		try{
			currentCon =ConnectionManager.getInstance().getConnection();
			Statement stmt =currentCon.createStatement();
			rs= stmt.executeQuery(statement);
			
			
			while(rs.next()){
			
			
			doc.setDocId(rs.getInt("docId")); 
			doc.setComment(rs.getString("comment"));
			doc.setDocName(rs.getString("docName"));
			doc.setRating(rs.getInt("totalRating"));
			doc.setRatingCount(rs.getInt("ratingCount"));
			doc.setTag(rs.getString("tag"));
			doc.setTitle(rs.getString("title"));
			doc.setUploadDate(rs.getDate("uploadDate"));
			
			user.setUserName(rs.getString("uploader"));
			doc.setUploader(user.getUserName());	
			
			//System.out.println(doc.toString());
			}
			
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			
			
			System.out.println("GetDocInfo Failed "+e);
		}
		
		return doc;

	}
public static boolean deleteFile(String filePath) {
	
	String query ="DELETE  FROM DOCUMENTS WHERE filePath=?";
	PreparedStatement stmt = null;
	boolean successful=false;
	try{
		currentCon =ConnectionManager.getInstance().getConnection();
		
		stmt = currentCon.prepareStatement(query);
		stmt.setString(1, filePath);
		int ret =stmt.executeUpdate();
		if(ret !=0 ){
			successful= true;
		}
		
		stmt.close();
		
	}
	
	catch(Exception e){
		System.out.println("deleteFile  Failed "+e);
	}
	return successful;
}
	
public static ArrayList<DocumentBean> getUserUploads(String userName){
	ArrayList<DocumentBean> documentList = new ArrayList<DocumentBean>();
		
	String statement ="SELECT * FROM DOCUMENTS WHERE uploader='" +userName+ "'";

	try{
		currentCon =ConnectionManager.getInstance().getConnection();
		Statement stmt =currentCon.createStatement();
		rs= stmt.executeQuery(statement);
		
	
		while (rs.next()){
		
		UserBean user = new UserBean();
		DocumentBean doc =new DocumentBean();
		doc.setDocId(rs.getInt("docId"));
	
		doc.setComment(rs.getString("comment"));
		doc.setDocName(rs.getString("docName"));
		doc.setRating(rs.getInt("totalRating"));
		doc.setRatingCount(rs.getInt("ratingCount"));
		doc.setTag(rs.getString("tag"));
		doc.setTitle(rs.getString("title"));
		doc.setUploadDate(rs.getDate("uploadDate"));
		
		
		user.setUserName(rs.getString("uploader"));
		doc.setUploader(user.getUserName());
		
		doc.setDocPath(rs.getString("filePath"));
		documentList.add(doc);
		
		}
		
		rs.close();
		stmt.close();
	}
	catch(Exception e){
		
		
		System.out.println("GetUserUploads Failed "+e);
	}
	
	
	
	return documentList;
	
}
//if successfully rated method returns row count which is one , otherwise method returns 0.
public static int rateDocument(int DocId , int rating){
	int ret = 0;
	String statement = "UPDATE DOCUMENTS SET ratingCount = ratingCount+1 , totalRating = totalRating+"+rating+" WHERE docId="+DocId;
	try{
		currentCon =ConnectionManager.getInstance().getConnection();
		Statement stmt =currentCon.createStatement();
		ret =stmt.executeUpdate(statement);
		
		stmt.close();
	}
	
	
	catch(Exception e){
		System.out.println("Cannot rate document : "+ e);
	}
	
	return ret;
	
}

public static double getUploaderRating(int DocumentId) {
	double avg = 0;
	String statement ="SELECT * FROM USERS, DOCUMENTS WHERE USERS.USERNAME = DOCUMENTS.UPLOADER  AND DOCUMENTS.DOCID = "+DocumentId;
	try{
		currentCon =ConnectionManager.getInstance().getConnection();
		Statement stmt =currentCon.createStatement();
		//System.out.println("query is " +statement);
		rs= stmt.executeQuery(statement);
		
		while(rs.next()){
		double total =rs.getInt("TOTALRATING");
		double count =rs.getInt("RATINGCOUNT");
		if(count != 0)
		avg =total/count;
		else
		avg=0;
		}
		rs.close();
		stmt.close();
	}
	catch(Exception e){
		System.out.println("Cannot get uploaders rating "+ e);	
	}
	return avg;
}

}
